﻿using System;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Collections.Generic;
using T.ServiceComponent.DAC.Service;
using T.ServiceComponent.DataAccessEx.Core;
using System.Collections.Concurrent;

namespace T.FAS.Runtime.Base.Dao
{
    public delegate void RegistUserDefineTypeDelegate(Type type);

    public class DaoUtil
    {
        #region 内部字典
        public const string DeleteSQLWithNoParam = "DELETE FROM {0} WHERE {1}='{2}'";
        public const string DeleteSQL = "DELETE FROM {0} WHERE {1}=@{2}";
        public const string BatchDeleteSQL = "DELETE FROM {0} WHERE {1} in ({2})";
        public const string BatchDeleteByParentForeignKeySQL = "DELETE FROM {0} WHERE {1} in (select {2} from {3} where {4} in ({5}))";

        public static HashSet<string> MySQLKeyWords = new HashSet<string>
        {
            "PRECISION".ToLower(),
            "KEY".ToLower(),
        };

        public static HashSet<string> PGKeyWords = new HashSet<string>
        {
            "PRECISION".ToLower(),
            "KEY".ToLower(),
        };

        public static ConcurrentBag<Type> ValidType = new ConcurrentBag<Type>
        { 
            /*布尔类型*/
            typeof(bool),
            typeof(bool?),
            /*数值类型*/
            typeof(int),
            typeof(int?),
            typeof(long),
            typeof(long?),
            typeof(float),
            typeof(float?),
            typeof(double),
            typeof(double?),
            typeof(decimal),
            typeof(decimal?),
            /*日期类型*/
            typeof(DateTime),
            typeof(DateTime?),
            /*字符串*/ 
            typeof(string),
            /*二进制类型*/
            typeof(byte[]),
        };

        public static ConcurrentDictionary<Type, TDbDataType> TypeMapping = new ConcurrentDictionary<Type, TDbDataType>
        (
            new Dictionary<Type, TDbDataType> {
                //bool值特殊处理
                {typeof(bool),  TDbDataType.Int},
                {typeof(bool?),  TDbDataType.Int},
                {typeof(int),  TDbDataType.Int},
                {typeof(int?),  TDbDataType.Int},
                {typeof(long),  TDbDataType.Int},
                {typeof(long?),  TDbDataType.Int},
                {typeof(float),  TDbDataType.Decimal},
                {typeof(float?),  TDbDataType.Decimal},
                {typeof(double),  TDbDataType.Decimal},
                {typeof(double?),  TDbDataType.Decimal},
                {typeof(decimal),  TDbDataType.Decimal},
                {typeof(decimal?),  TDbDataType.Decimal},
                {typeof(DateTime),  TDbDataType.DateTime},
                {typeof(DateTime?),  TDbDataType.DateTime},
                {typeof(string),  TDbDataType.NVarChar},
                {typeof(byte[]),  TDbDataType.Blob},
            }
        );

        public static ConcurrentDictionary<Type, Func<object, object>> ForceTypeCast = new ConcurrentDictionary<Type, Func<object, object>>
        (
            new Dictionary<Type, Func<object, object>> {
                {
                    typeof(bool),
                    obj =>
                        {
                            if (bool.TryParse(obj.ToString(), out bool result))
                                return result;
                            else
                            {
                                //兼容存储0 1值的情况
                                if (int.TryParse(obj.ToString(), out int intResult))
                                {
                                    return intResult == 1 ? true : false;
                                }
                            }
                            return default(bool);
                        }
                }
            }
        );

        #endregion

        #region Event
        protected static event RegistUserDefineTypeDelegate RegistUserDefineTypeEvent;
        #endregion

        static DaoUtil()
        {
            RegistUserDefineTypeEvent += AddUserDefinedType;
        }

        /// <summary>
        /// 获取实体类中的排除列（DaoService处理不了的列）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<string> GetExceptionColumn<T>(List<string> manualeExcludeColumn = null)
        {
            Type type = typeof(T);
            List<PropertyInfo> props; List<FieldInfo> fields;
            GetDbPropsAndFields<T>(out props, out fields, false);
            List<string> Columns = ColumnConcat(fields.Select(filed => filed.Name), props.Select(prop => prop.Name), false).ToList();
            if (manualeExcludeColumn != null)
                return ColumnConcat(Columns, manualeExcludeColumn, false).ToList();
            else
                return Columns;
        }

        /// <summary>
        /// DataTable转换实体类集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <param name="exceptionColumn"></param>
        /// <returns></returns>
        public static List<T> ConvertToEntity<T>(DataTable dt, List<string> exceptionColumn = null) where T : new()
        {
            if (dt == null)
                return null;
            if (exceptionColumn == null) exceptionColumn = new List<string>();
            List<T> list = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(ConvertToEntity<T>(dr, exceptionColumn));
            }
            return list;
        }

        /// <summary>
        /// 执行插入操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entitys"></param>
        /// <param name="daoService"></param>
        public static void ExecuteInsertSQL<T>(List<T> entitys, DaoService<T> daoService)
        {
            if (entitys == null || entitys.Count == 0) return;
            string[] InsertSQLs; IDbDataParameter[][] Params;
            FillInsertSQLAndParams(entitys, daoService, out InsertSQLs, out Params);
            daoService.DB.ExecSqlStatement(InsertSQLs, Params);
        }
        public static bool IntConvertBool(object data, int isTrueValue = 1)
        {
            if (data == DBNull.Value)
                return false;
            else
            {
                return Convert.ToInt16(data).Equals(Convert.ToInt16(isTrueValue)) ? true : false;
            }
        }
        public static int SetIntDefaultValue(object data, int defaultValue)
        {
            if (data == DBNull.Value)
                return defaultValue;
            else
            {
                return Convert.ToInt16(data);
            }
        }

        public static string GetIfNullFuncName<T>(DaoService<T> daoService)
        {
            if (daoService.DB.DbType == TDbType.PostgreSQL)
            {
                return "coalesce";
            }
            return "ifnull";
        }
        #region Private
        /// <summary>
        /// 生成Insert插入语句集合及Params集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entitys"></param>
        /// <param name="daoService"></param>
        private static void FillInsertSQLAndParams<T>(List<T> entitys, DaoService<T> daoService, out string[] insertSQLs, out IDbDataParameter[][] parms)
        {
            entitys = entitys.Where(entity => entity != null).ToList(); //跳过所有的Null项
            insertSQLs = new string[entitys.Count];
            parms = new IDbDataParameter[entitys.Count][];
            int index = 0;
            foreach (var col in entitys)
            {
                var sql = GetInsertSQL<T>(daoService, daoService.TableName, daoService.ExceptColumnName);
                insertSQLs[index] = sql;
                List<IDbDataParameter> dbDataParameter = GetParams(col, daoService, daoService.ExceptColumnName);
                parms[index] = dbDataParameter.ToArray();
                index++;
            }
        }

        /// <summary>
        /// 生成Query语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="exceptionColumn"></param>
        /// <returns></returns>
        private static string GetInsertSQL<T>(DaoService<T> daoService, string tableName, List<string> exceptionColumn = null)
        {
            var list = GetDbColumn<T>(exceptionColumn);
            List<string> cols = new List<string>();
            //生成@param的sql中不能带着@`Precision`,必须是@Precision
            List<string> parmcols = new List<string>();
            foreach (var col in list)
            {
                //列名统一用小写
                string colLower = col.ToLower();
                if (daoService.DB.DbType == TDbType.MySql && MySQLKeyWords.Contains(colLower))
                {
                    cols.Add($"`{colLower}`");
                }
                else if (daoService.DB.DbType == TDbType.PostgreSQL && PGKeyWords.Contains(colLower))
                {
                    cols.Add($"\"{colLower}\"");
                }
                else
                {
                    cols.Add(colLower);
                }
                parmcols.Add(col);
            }
            return $"insert into {tableName} ({string.Join(",", cols)}) values(@{string.Join(",@", parmcols)})";
        }

        /// <summary>
        /// DataRow转换实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <param name="exceptionColumn"></param>
        /// <returns></returns>
        private static T ConvertToEntity<T>(DataRow dr, List<string> exceptionColumn = null) where T : new()
        {
            try
            {
                List<PropertyInfo> props; List<FieldInfo> fields;
                GetDbPropsAndFields<T>(out props, out fields);
                //清理手动排除列
                props.RemoveAll(prop => exceptionColumn.Contains(prop.Name));
                fields.RemoveAll(field => exceptionColumn.Contains(field.Name));

                T instance = (T)Activator.CreateInstance(typeof(T));
                List<string> columNames = GetColumnNames(dr.Table);
                //构造属性参数
                foreach (var prop in props)
                {
                    Type propType = prop.PropertyType;
                    var propName = TryGetORMPhysicalName(prop, out ORMPhysicalNameAttribute attr) ? attr.PhysicalName : prop.Name;
                    var physicalField = columNames.FirstOrDefault(col => string.Equals(col, propName, StringComparison.OrdinalIgnoreCase));
                    if (physicalField != null)
                    {
                        object value = dr[physicalField];
                        if (dr[physicalField] == DBNull.Value)
                        {
                            value = propType.IsValueType ? Activator.CreateInstance(propType) : null;
                        }
                        else if (ForceTypeCast.ContainsKey(propType))
                        {
                            value = ForceTypeCast[propType](dr[physicalField]);
                        }
                        prop.SetValue(instance, value);
                    }
                }
                //构造字段参数
                foreach (var field in fields)
                {
                    Type fieldType = field.FieldType;
                    var fieldName = TryGetORMPhysicalName(field, out ORMPhysicalNameAttribute attr) ? attr.PhysicalName : field.Name;
                    var physicalField = columNames.FirstOrDefault(col => string.Equals(col, fieldName, StringComparison.OrdinalIgnoreCase));
                    if (physicalField != null)
                    {
                        object value = dr[physicalField];
                        if (dr[physicalField] == DBNull.Value)
                        {
                            value = fieldType.IsValueType ? Activator.CreateInstance(fieldType) : null;
                        }
                        else if (ForceTypeCast.ContainsKey(fieldType))
                        {
                            value = ForceTypeCast[fieldType](dr[physicalField]);
                        }
                        field.SetValue(instance, value);
                    }
                }
                return instance;
            }
            catch (Exception e)
            {

                throw new Exception(e.Message, e);
            }
        }

        private static List<string> GetColumnNames(DataTable dt)
        {
            List<string> result = new List<string>();
            if (dt == null) return result;
            foreach (DataColumn column in dt.Columns)
            {
                result.Add(column.ColumnName);
            }
            return result;
        }

        private static bool TryGetORMPhysicalName(PropertyInfo propertyInfo, out ORMPhysicalNameAttribute attr)
        {
            attr = null;
            try
            {
                attr = propertyInfo.GetCustomAttribute(typeof(ORMPhysicalNameAttribute)) as ORMPhysicalNameAttribute;
                if (attr == null) return false;
                else return true;
            }
            catch
            {
                return false;
            }
        }

        private static bool TryGetORMPhysicalName(FieldInfo fieldInfo, out ORMPhysicalNameAttribute attr)
        {
            attr = null;
            try
            {
                attr = fieldInfo.GetCustomAttribute(typeof(ORMPhysicalNameAttribute)) as ORMPhysicalNameAttribute;
                if (attr == null) return false;
                else return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 返回实体类最终映射到物理表中的所有列集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="exceptionColumn"></param>
        /// <returns></returns>
        public static List<string> GetDbColumn<T>(List<string> exceptionColumn = null)
        {
            Type type = typeof(T);
            List<PropertyInfo> props; List<FieldInfo> fields;
            GetDbPropsAndFields<T>(out props, out fields);
            List<string> propNames = new List<string>();
            List<string> fieldNames = new List<string>();

            foreach (var field in fields)
            {
                var fieldName = TryGetORMPhysicalName(field, out ORMPhysicalNameAttribute attr) ? attr.PhysicalName : field.Name;
                fieldNames.Add(fieldName);
            }
            foreach (var prop in props)
            {
                var propName = TryGetORMPhysicalName(prop, out ORMPhysicalNameAttribute attr) ? attr.PhysicalName : prop.Name;
                propNames.Add(propName);
            }

            List<string> Columns = ColumnConcat(propNames, fieldNames).ToList();
            if (exceptionColumn != null)
                Columns.RemoveAll(col => exceptionColumn.Contains(col));
            return Columns;
        }

        /// <summary>
        /// 获取数据库参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="daoService"></param>
        /// <param name="exceptionColumn"></param>
        /// <returns></returns>
        private static List<IDbDataParameter> GetParams<T>(T entity, DaoService<T> daoService, List<string> exceptionColumn = null)
        {
            Type type = typeof(T);
            List<PropertyInfo> props; List<FieldInfo> fields;
            GetDbPropsAndFields<T>(out props, out fields);
            //清理手动排除列
            props.RemoveAll(prop => exceptionColumn != null && exceptionColumn.Contains(prop.Name));
            fields.RemoveAll(field => exceptionColumn != null && exceptionColumn.Contains(field.Name));
            //声明DBParm集合
            List<IDbDataParameter> dbDataParameters = new List<IDbDataParameter>();
            //构造属性参数
            foreach (var prop in props)
            {
                TDbDataType paramType = TypeMapping[prop.PropertyType];
                var propName = TryGetORMPhysicalName(prop, out ORMPhysicalNameAttribute attr) ? attr.PhysicalName : prop.Name;
                IDbDataParameter param;
                //低代码迁移PG修改-2025.03.05: PG获取参数值时，不需要指定参数类型
                object paramValue = GetParamValue(daoService, prop, entity);
                if (daoService.DB.DbType == TDbType.PostgreSQL)
                {
                    if (prop.PropertyType == typeof(bool))
                    {
                        param = daoService.DB.MakeInParam($"@{propName}", TDbDataType.Int, paramValue);
                    }
                    else
                    {
                        param = daoService.DB.MakeInParam($"@{propName}", paramValue);
                    }
                }
                else
                    param = daoService.DB.MakeInParam($"@{propName}", paramType, paramValue);
                dbDataParameters.Add(param);
            }
            //构造字段参数
            foreach (var field in fields)
            {
                TDbDataType paramType = TypeMapping[field.FieldType];
                var fieldName = TryGetORMPhysicalName(field, out ORMPhysicalNameAttribute attr) ? attr.PhysicalName : field.Name;
                IDbDataParameter param;
                //低代码迁移PG修改-2025.03.05: PG获取参数值时，不需要指定参数类型
                object paramValue = GetParamValue(daoService, field, entity);
                if (daoService.DB.DbType == TDbType.PostgreSQL)
                {
                    if (field.FieldType == typeof(bool))
                    {
                        param = daoService.DB.MakeInParam($"@{fieldName}", TDbDataType.Int, paramValue);
                    }
                    else
                    {
                        param = daoService.DB.MakeInParam($"@{fieldName}", paramValue);
                    }
                }
                else
                    param = daoService.DB.MakeInParam($"@{fieldName}", paramType, paramValue);
                dbDataParameters.Add(param);
            }
            return dbDataParameters;
        }

        /// <summary>
        /// 获取可以映射到Table列的属性 字段
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="propertyInfos"></param>
        /// <param name="fieldInfos"></param>
        /// <param name="flag">结果标识：true:返回Table列， false:返回排除列</param>
        /// <param name="ExceptionColumn">手动排除列</param>
        private static void GetDbPropsAndFields<T>(out List<PropertyInfo> propertyInfos, out List<FieldInfo> fieldInfos, bool flag = true)
        {
            Type type = typeof(T);
            //只取当前类的属性以及字段DeclaredOnly;
            propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public).Where(prop => prop.CanRead && prop.CanWrite).ToList();
            fieldInfos = type.GetFields(BindingFlags.Instance | BindingFlags.Public).Where(field => !field.Name.EndsWith("k__BackingField")).ToList();
            if (flag)
            {
                Func<Type, bool> GetValidTypeFunc = t =>
                {
                    RegistUserDefineTypeEvent(t);
                    return ValidType.Contains(t)
                            || t.IsEnum;
                };
                propertyInfos = propertyInfos.Where(prop => GetValidTypeFunc(prop.PropertyType)).ToList();
                fieldInfos = fieldInfos.Where(field => GetValidTypeFunc(field.FieldType)).ToList();
            }
            else
            {
                Func<Type, bool> GetInvalidTypeFunc = t =>
                {
                    RegistUserDefineTypeEvent(t);
                    return !(ValidType.Contains(t)
                            || t.IsEnum);
                };
                propertyInfos = propertyInfos.Where(prop => GetInvalidTypeFunc(prop.PropertyType)).ToList();
                fieldInfos = fieldInfos.Where(field => GetInvalidTypeFunc(field.FieldType)).ToList();
            }
        }

        /// <summary>
        /// 强制处理PG数据库的枚举类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="daoService"></param>
        /// <param name="fieldInfo"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        private static object GetParamValue<T>(DaoService<T> daoService, FieldInfo fieldInfo, T entity)
        {
            object val = fieldInfo.GetValue(entity);
            if (daoService.DB.DbType == TDbType.PostgreSQL)
            {
                if (fieldInfo.FieldType.IsEnum)
                {
                    if (Enum.GetUnderlyingType(fieldInfo.FieldType) == typeof(long))
                    {
                        return (long)val;
                    }
                    else
                    {
                        return (int)val;
                    }
                }
                else if (fieldInfo.FieldType == typeof(bool))
                {
                    return (bool)val ? 1 : 0;
                }
                else if (val is DateTime dateTime && dateTime.Year == 1)
                {
                    return DateTime.Now;
                }
                else if (val == null)
                {
                    val = DBNull.Value;
                }
            }
            return val;
        }

        /// <summary>
        /// 强制处理PG数据库的枚举类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="daoService"></param>
        /// <param name="propInfo"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        private static object GetParamValue<T>(DaoService<T> daoService, PropertyInfo propInfo, T entity)
        {
            object val = propInfo.GetValue(entity);
            if (daoService.DB.DbType == TDbType.PostgreSQL)
            {
                if (propInfo.PropertyType.IsEnum)
                {
                    if (Enum.GetUnderlyingType(propInfo.PropertyType) == typeof(long))
                    {
                        return (long)val;
                    }
                    else
                    {
                        return (int)val;
                    }
                }
                else if (propInfo.PropertyType == typeof(bool))
                {
                    return (bool)val ? 1 : 0;
                }
                else if (val is DateTime dateTime && dateTime.Year == 1)
                {
                    return DateTime.Now;
                }
                else if (val == null)
                {
                    val = DBNull.Value;
                }
            }
            return val;
        }

        private static void SetIsNullable(IDbDataParameter parameter)
        {
            // 动态判断是否为NpgsqlParameter（无需直接引用Npgsql.dll）
            if (parameter.GetType().FullName == "Npgsql.NpgsqlParameter")
            {
                Type paramType = parameter.GetType();
                PropertyInfo property = paramType.GetProperty(nameof(IDbDataParameter.IsNullable));
                if (property != null && property.CanWrite)
                {
                    property.SetValue(parameter, true);
                }
                else
                {
                    throw new InvalidOperationException("IsNullable property is not writable");
                }
            }
            else
            {
                throw new ArgumentException("Parameter is not NpgsqlParameter type");
            }
        }


        /// <summary>
        ///  去重连接两个ColumnName集合
        /// </summary>
        /// <param name="orgin"></param>
        /// <param name="new"></param>
        /// <param name="ignoreCase">是否忽略大小写 true:忽略 false:不忽略</param>
        /// <returns></returns>
        private static IEnumerable<string> ColumnConcat(IEnumerable<string> orgin, IEnumerable<string> @new, bool ignoreCase = true)
        {
            string[] tmp = new string[orgin.Count()];
            var resultList = orgin.ToList();
            resultList.CopyTo(tmp);
            foreach (var newCol in @new)
            {
                bool f = !tmp.Any(orginCol =>
                                    ignoreCase
                                    ? string.Equals(orginCol, newCol, StringComparison.OrdinalIgnoreCase)
                                    : string.Equals(orginCol, newCol));
                if (f)
                {
                    resultList.Add(newCol);
                }
            }
            return resultList;
        }

        /// <summary>
        /// 注册用户自定义可以映射到物理表的类型（目前主要是枚举）
        /// </summary>
        private static void AddUserDefinedType(Type type)
        {
            if (ValidType.Contains(type))
                return;
            if (type.IsEnum)    //注册枚举
                RegistEnum(type);
        }

        /// <summary>
        /// 注册枚举
        /// </summary>
        /// <param name="type"></param>
        private static void RegistEnum(Type type)
        {
            if (!type.IsEnum)
                return;
            if (!ValidType.Contains(type))
                ValidType.Add(type);
            TypeMapping.TryAdd(type, TDbDataType.Int);
            ForceTypeCast.TryAdd(type, obj =>
            {
                try
                {
                    var TypeName = type.Name;   //For Debug;
                    if (obj is int intVal)
                        return Enum.ToObject(type, intVal);
                    else
                    {
                        var strVal = obj.ToString();
                        if (!string.IsNullOrEmpty(strVal))
                        {
                            if (int.TryParse(strVal, out int intVal2))
                            {
                                return Enum.ToObject(type, intVal2);
                            }
                            return Enum.Parse(type, strVal, true);
                        }
                    }
                    throw new Exception($"DaoUtil枚举类型:{type}转换失败，输入值:{obj}");
                }
                catch (Exception e)
                {
                    return Activator.CreateInstance(type);
                }
            }
            );
        }

        #endregion
    }
}
